<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ page import="java.sql.*"  %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>计算营业额</title>
 <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
</head>
<%
request.setCharacterEncoding("UTF-8");
String restaurant_name = request.getParameter("restaurant_name");
String receptionist_name = request.getParameter("customer_name");
String startDate = request.getParameter("startDate");
String endDate = request.getParameter("endDate");

//连接数据库——这里是关于图表的数据
Class.forName("org.mariadb.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mariadb://localhost:3306/mealdb?user=root&password=1230321");
Statement stmt = conn.createStatement();

String sql ="SELECT SUM(d.num * m.price) AS total_sales, d.meal " +
        "FROM dish d, meal_detail m, customers c " +
        "WHERE d.meal = m.`name` AND c.`name` = d.customer_name AND c.res = '" + restaurant_name + "' AND dish_time BETWEEN '" + startDate + "' AND '" + endDate + "' " +
        "GROUP BY d.meal";
System.out.print("sql= "+sql);
ResultSet rs = stmt.executeQuery(sql);


//连接数据库——这里是关于总营业额的数据
Class.forName("org.mariadb.jdbc.Driver");
Connection conn1 = DriverManager.getConnection("jdbc:mariadb://localhost:3306/mealdb?user=root&password=1230321");
Statement stmt1 = conn1.createStatement();

//构造SQL写入数据到数据表
/*SELECT SUM(d.num * m.price) AS total_sales
FROM dish d,meal_detail m
WHERE d.meal = m.`name` AND dish_time BETWEEN '2022-1-2' AND '2024-8-4';**/
String sql1 ="SELECT SUM(d.num * m.price) AS total_sales ,d.meal,m.price,d.num  FROM dish d,meal_detail m ,customers c WHERE d.meal = m.`name` AND c.`name` = d.customer_name AND c.res = '"+restaurant_name+"' AND dish_time BETWEEN '"+startDate+"' AND '"+endDate+"';";
//"SELECT SUM(d.num * m.price) AS total_sales ,d.meal,m.price,d.num  FROM dish d,meal_detail m ,customers c WHERE d.meal = m.`name` AND c.`name` = d.customer_name AND c.res = '"+restaurant_name+"' AND dish_time BETWEEN '"+startDate+"' AND '"+endDate+"';";
System.out.print("sql= "+sql1);
//stmt.executeQuery(sql);
//out.print("响应行数： "+stmt.executeQuery(sql));


ResultSet rs1 = stmt.executeQuery(sql1);
%>
<body>
<%
//检查ResultSet是否有数据，并获取总销售额
double totalSales = 0;
if (rs1.next()) {
 totalSales = rs1.getDouble("total_sales");
 // 输出总销售额到页面或进行其他处理
%>

<div> 
<h2>当前营业额为:<%=totalSales %></h2>

<%
} else {
 // 如果没有数据，则处理相应的情况
 out.print("没有查询到销售数据。");
}


stmt.close();
conn.close();

stmt1.close();
conn1.close();

//显示计算结果
//out.print("修改完毕！");
//response.sendRedirect("viewOrders.jsp?customer_name="+receptionist_name+"&restaurant_name="+restaurant_name);

%>


<div> 
    <h2>营业额图表</h2>
    <div id="salesChart" style="width: 900px; height: 500px;"></div>
</div>

<script>
    // 加载Google Charts库
    google.charts.load('current', {'packages':['corechart']});
    google.charts.setOnLoadCallback(drawChart);

    // 绘制图表的函数
    function drawChart() {
        // 创建数据表
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Meal');
        data.addColumn('number', 'Sales');

        // 添加数据行，这里我们从数据库查询到的数据
        var rows = [
            <% while (rs.next()) { %>
                ['<%= rs.getString("meal") %>', <%= rs.getDouble("total_sales") %>],
            <% } %>
        ];
        data.addRows(rows);

        // 设置图表选项
        var options = {
            title: '不同菜品的营业额',
            hAxis: {title: '营业额', minValue: 0},
            vAxis: {title: '菜名', minValue: 0},
            legend: 'none'
        };

        // 绘制图表
        var chart = new google.visualization.BarChart(document.getElementById('salesChart'));
        chart.draw(data, options);
    }
</script>
<a href='receptionistIndex.jsp?restaurant_name=<%=restaurant_name%>&customer_name=<%=receptionist_name%>'>返回首页</a>
</div>

</body>
</html>